<?php

namespace App\Http\Controllers;

use App\Exceptions\ApiException;
use App\Exports\BookCatalog8;
use App\Exports\BookCatalogLY;
use App\Jobs\GetFileSize;
use App\Jobs\DelOssPdf;
use App\Jobs\RemoveDelCatalogData\CopyTable;
use App\Jobs\SlowExeCatalog8;
use App\Jobs\Test;
use App\Jobs\UpdateBtPageNum;
use App\Jobs\UpdatePaperBtCount;
use App\Models\ActionLog;
use App\Models\BookCatalog;
use App\Models\BookCatalogPdf;
use App\Models\CompanyUser;
use App\Models\DownloadJobQueue;
use App\Models\PartCompany;
use App\Models\PartCompanyClassify;
use App\Models\PartCompanyUser;
use App\Models\Project;
use App\Models\ProjectClassify;
use App\Models\User;
use App\Models\UserLoginInfo;
use App\Services\AttachmentService;
use App\Services\BaseService;
use App\Services\BookCatalogService;
use App\Services\BookTitleService;
use App\Services\CommonService;
use App\Services\DownloadJobService;
use App\Services\ProjectClassifyService;
use App\Services\Utils\VerifyDate;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Schema;
use Illuminate\Support\Facades\Storage;
use Maatwebsite\Excel\Facades\Excel;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Writer\Pdf\Mpdf;
use Symfony\Component\Process\Exception\ProcessFailedException;
use Symfony\Component\Process\Process;

class TestController extends Controller
{
    const START_TIME = '10:15';

    public $archivesNoKey;

    public function __construct()
    {
        parent::__construct();
    }

    public function index(Request $request)
    {

    }

    /**
     * 执行队列
     *
     * @return string
     * @author: lxk
     * @date: 2025-07-05 11:10
     */
    public function runCommand() : string
    {
        $process = new Process(['php', 'artisan', 'queue:work']);
        $process->setTimeout(3600);
        $process->setWorkingDirectory(base_path());
        try {
            $process->mustRun(); // 执行命令
            $output = $process->getOutput();
            return "成功：\n" . $output;
        } catch (ProcessFailedException $e) {
            return "失败：" . $e->getMessage();
        }
    }


    /**
     * @param Request $request
     * @throws \Psr\Container\ContainerExceptionInterface
     * @throws \Psr\Container\NotFoundExceptionInterface
     * @author: lxk
     * @date: 2025-07-03 14:41
     */
    public function getOssToLocal()
    {
        $pdfTables = CommonService::getSubTables("book_catalog_pdfs_47");
        foreach ($pdfTables as $pdfTable) {
            $tableParams = CommonService::getTableParams($pdfTable);
            $projectId = PartCompany::query()->where('id', $tableParams['id1'])->value('project_id');
            DB::table($pdfTable)
                ->where('is_local', 0)
                ->whereNull('deleted_at')
                ->where('book_catalog_id', '>', 0)
                ->chunkById(50, function ($list) use ($projectId, $tableParams, $pdfTable) {
                    foreach ($list as $val) {
                        $ossUrl     = str_replace('https://tieluoss.oss-cn-chengdu.aliyuncs.com/', '', $val->file_url);
                        info($ossUrl);
                        $newPdfPath = $this->getNewPdfPath($projectId, $tableParams['id1'], $tableParams['id2'], $val->book_title_id, $val->book_catalog_id);
                        $newPdfPath = $newPdfPath . basename($ossUrl);
                        $attachment = new AttachmentService();
                        $attachment->downloadOssToLocal($ossUrl, $newPdfPath, $pdfTable, $val->id);
                    }
                });
        }
    }

    /**
     * 获取一个新的PDF地址
     *
     * @param $projectId
     * @param $partCompanyId
     * @param $projectClassifyId
     * @param $bookTitleId
     * @param $bookCatalogId
     * @return string
     * @author: lxk
     * @date: 2025-07-04 8:52
     */
    public function getNewPdfPath($projectId, $partCompanyId, $projectClassifyId, $bookTitleId, $bookCatalogId): string
    {
        return "pro{$projectId}/part{$partCompanyId}/unitPro{$projectClassifyId}/btNo{$bookTitleId}/caNo{$bookCatalogId}/";
    }


    public function getUnitProFileFolder(Request $request)
    {
        $partCompanyId     = $request->input('part_company_id');
        $projectClassifyId = $request->input('project_classify_id');
        $isNeedZip         = $request->input('is_need_zip'); //是不是需要压缩

        $projectClassifyArray = json_decode(redis()->get('29924127001'), true);
        $projectClassifyIds   = $this->getAllChildIds($partCompanyId, $projectClassifyId);
        $fileCount            = 0; //需要生成几个单位工程的excel
        foreach ($projectClassifyIds as $projectClassifyId) {
            $fileName     = $this->getFileNameByProId($projectClassifyId, $projectClassifyArray);
            $bookTitleIds = $this->getIds($partCompanyId, $projectClassifyId);
            if ($bookTitleIds) {
                $fileCount++;
                (new BookCatalogService())->exportExcel($partCompanyId, $projectClassifyId, $bookTitleIds, $fileName, $isNeedZip);
            }
        }
        $partCompany = PartCompany::query()->where('id', $partCompanyId)->first();
        $projectCode = Project::query()->where('id', $partCompany->project_id)->value('document_number');
        $dirName     = "excels/{$projectCode}_{$partCompany->title}_卷内目录";
        CommonService::createDir($dirName);

        (new DownloadJobService())->addNewData($partCompanyId, $projectClassifyId, [], $dirName, $fileCount, '导出【卷内】目录', 1, $isNeedZip); //添加到任务表
    }

    /**
     * 获取当前单位工程的全部下级
     *
     * @param $partCompanyId
     * @param $projectClassifyId
     * @return array
     * @author: lxk
     * @date: 2025-05-18 17:14
     */
    public function getAllChildIds($partCompanyId, $projectClassifyId): array
    {
        $redisKey = "childrenBts:{$partCompanyId}:{$projectClassifyId}";
        $tables   = redis()->smembers($redisKey); //获取当前参建单位下的案卷全部的分表
        $childIds = [];
        foreach ($tables as $table) {
            $info       = CommonService::getTableParams($table);
            $childIds[] = $info['id2'];
        }
        sort($childIds);

        return $childIds;
    }

    /**
     * 根据单位工程id获取生成的excel文件名
     *
     * @param $proClassId
     * @param $data
     * @return string
     * @author: lxk
     * @date: 2025-05-18 16:51
     */
    public function getFileNameByProId($proClassId, $data): string
    {
        $nowProClassInfo = $this->findInArray($data, 'id', $proClassId);  //获取当前信息
        $fileName        = $nowProClassInfo['title'];
        if ($nowProClassInfo['parent_id']) { //如果有父级
            $previousLevel1 = $this->findInArray($data, 'classify_id', $nowProClassInfo['parent_id']);
            $fileName       = "{$previousLevel1['title']}_{$fileName}";
            if ($previousLevel1['parent_id']) { //父级还有父级
                $previousLevel2 = $this->findInArray($data, 'classify_id', $previousLevel1['parent_id']);
                $fileName       = "{$previousLevel2['title']}_{$fileName}";
                if ($previousLevel2['parent_id']) { //父级还有父级
                    $previousLevel3 = $this->findInArray($data, 'classify_id', $previousLevel2['parent_id']);
                    $fileName       = "{$previousLevel3['title']}_{$fileName}";
                }
            }
        }
        return $fileName;
    }

    /**
     * 获取一个二维关联数组中，指定键名和键值的那组数据
     *
     * @param $array
     * @param $key
     * @param $value
     * @return null
     * @author: lxk
     * @date: 2025-05-18 17:33
     */
    public function findInArray($array, $key, $value)
    {
        foreach ($array as $item) {
            if (isset($item[$key]) && $item[$key] == $value) {
                return $item;
            }
        }
        return null; // 如果没有找到返回 null
    }

    //递归方法，获取当前参建单位的全部父级
    public function getIds($partCompanyId, $projectClassifyId)
    {
        $bookTitleIds = CommonService::getBookTitleInstance($partCompanyId, $projectClassifyId)->pluck('id');

        return $bookTitleIds->isNotEmpty() ? $bookTitleIds->toArray() : [];
    }

    public static function getProjectClassifyKey($partCompanyId): string
    {
        return $partCompanyId . user_id() . get_ip_num();
    }

    public function findParentMenus($menuArray, $currentId, $path = [])
    {
        foreach ($menuArray as $item) {
            // 如果找到当前菜单，返回已记录的路径
            if ($item['id'] == $currentId) {
                return $path;
            }
            // 如果有子菜单，递归查找
            if (!empty($item['children'])) {
                $newPath   = $path;
                $newPath[] = $item; // 将当前项加入路径
                $result    = $this->findParentMenus($item['children'], $currentId, $newPath);
                if ($result !== null) {
                    return $result;
                }
            }
        }
        return null; // 未找到
    }


    public function updateCatalog()
    {
        $projectId          = 5; //石武客专（河北段）
        $partCompanyIds     = PartCompany::query()->where('project_id', $projectId)->pluck('id')->toArray();
        $projectClassifies  = ProjectClassify::query()
            ->whereIn('part_company_id', $partCompanyIds)
            ->where('major_code', 'Q')
            ->select('id', 'title', 'major_code')
            ->get()
            ->toArray();
        $projectClassifyIds = array_column($projectClassifies, 'id');
        $allTables          = DB::connection()->getDoctrineSchemaManager()->listTableNames(); //获取全部数据表名
        foreach ($allTables as $table) {
            $tableParams = CommonService::getTableParams($table);
            if (in_array($tableParams['id1'], $partCompanyIds) && in_array($tableParams['id2'], $projectClassifyIds)) {
                if (str_contains($table, 'catalogs')) { //包含桥的卷内表
                    Test::dispatch($table);
                }
            }
        }
    }


    /**
     * 修改字段长度
     */
    public function updateFiledAttr()
    {
        $tables = CommonService::getSubTables("book_catalogs");
        foreach ($tables as $table) {
            info($table);
            Test::dispatch($table);
        }
    }

    //添加一个字段
    public function addOneField()
    {
        $tables = CommonService::getSubTables('book_catalog_pdfs'); //获取全部数据表
        foreach ($tables as $table) {
            Test::dispatch($table);
        }
        return $this->success('', '操作成功！');
    }

    /**
     * 更新全部案卷的页次
     */
    public function updateBtPageNum()
    {
        $tables = CommonService::getSubTables("book_titles");
        foreach ($tables as $table) {
            $idInfo = CommonService::getTableParams($table);
            if ($idInfo['id1'] > 100 && $idInfo['id1'] <= 200) {
                $tableExt = "{$idInfo['id1']}_{$idInfo['id2']}";
                UpdateBtPageNum::dispatch($tableExt);
            }
        }
    }

    /**
     * 把失效的PDF从OSS删除，并把 book_catalog_pdfs_ 表的 各字段谁为 0 或 '' 并 deleted_at
     */
    public function delPdfFromOSS()
    {
        $tables = CommonService::getSubTables("book_catalog_pdfs");
        foreach ($tables as $table) {
            $idInfo = CommonService::getTableParams($table);
            $arr    = [117, 123];
            if (in_array($idInfo['id1'], $arr)) {
                $tableExt = "{$idInfo['id1']}_{$idInfo['id2']}";
                $list     = DB::table("book_catalogs_$tableExt as bc")
                    ->join("book_catalog_pdfs_$tableExt as pdf", 'bc.id', '=', 'pdf.book_catalog_id')
                    ->whereNotNull('bc.deleted_at') //被删除的卷内
                    ->whereNull('pdf.deleted_at') //扫描件没删除
                    ->select('bc.id as book_catalog_id', 'pdf.id as pdf_id', 'pdf.file_url')
                    ->get();
                if (!empty($list)) {
                    $list = $list->toArray();
                    foreach ($list as $val) {
                        $pdfTable = "book_catalog_pdfs_$tableExt";
                        DelOssPdf::dispatch($pdfTable, $val->pdf_id, $val->file_url);
                    }
                }
            }
        }
    }


    /**
     * @param $request
     * @throws \Doctrine\DBAL\Exception
     * @author: lxk
     * @date: 2024/4/17 16:02
     */
    public function getOssFileObjectSize()
    {
        $tables = CommonService::getSubTables("book_catalog_pdfs");
        foreach ($tables as $table) {
            if (!str_contains($table, '_pdfs_28') || !str_contains($table, '_pdfs_117')) {
                DB::table($table)->select(['id', 'file_url', 'created_at'])->chunkById(1000, function ($list) use ($table) {
                    foreach ($list as $val) {
                        if ($val->created_at > '2024-03-01 00:00:01') {
                            if (str_contains($val->file_url, 'pdfs')) {
                                $object = substr($val->file_url, strpos($val->file_url, 'pdfs'));
                            } elseif (str_contains($val->file_url, 'images')) {
                                $object = substr($val->file_url, strpos($val->file_url, 'images'));
                            } else {
                                $object = substr($val->file_url, strpos($val->file_url, 'files'));
                            }
                            GetFileSize::dispatch($table, $val->id, $object);
                        }
                    }
                });
            }
        }
    }

    /**
     *  把之前导入过PDF的卷内，后来重复导入卷内，卷内软删除，关联的PDF也失效，
     * 下面把PDF重新关联到新的卷内，book_title_id相等，serial_no相等
     *
     * @author: lxk
     * @date: 2024-10-25 14:39
     */
    public function createPdfLink()
    {
        //$projectId      = 8;
        $partCompanyIds = [30, 31, 32, 37];//PartCompany::query()->where('project_id', $projectId)->pluck('id')->toArray();
        $projectTables  = [];
        foreach ($partCompanyIds as $partCompanyId) {
            $partCompanyTables = CommonService::getSubTables("book_titles_{$partCompanyId}");
            $projectTables     = array_merge($projectTables, $partCompanyTables);
        }
        foreach ($projectTables as $table) {
            $idInfo            = CommonService::getTableParams($table);
            $partCompanyId     = $idInfo['id1'];
            $projectClassifyId = $idInfo['id2'];
            $hadDeletedList    = DB::table("book_catalogs_{$partCompanyId}_{$projectClassifyId}")
                ->whereNotNull('deleted_at') //被删除的卷内
                ->where('creator_id', '>', 0)
                ->select('id', 'serial_no', 'book_title_id')
                ->get()
                ->toArray();
            $notDeletedList    = DB::table("book_catalogs_{$partCompanyId}_{$projectClassifyId}")
                ->whereNull('deleted_at') //没被删除的卷内
                ->select('id', 'serial_no', 'book_title_id')
                ->get()
                ->toArray();
            foreach ($hadDeletedList as $val1) {
                $pdfInfo = DB::table("book_catalog_pdfs_{$partCompanyId}_{$projectClassifyId}")->where('book_catalog_id', $val1->id)->first();
                if ($pdfInfo) {
                    foreach ($notDeletedList as $val2) {
                        if (($val1->book_title_id == $val2->book_title_id) && ($val1->serial_no == $val2->serial_no)) {
                            info("book_catalog_pdfs_{$partCompanyId}_{$projectClassifyId}");
                            DB::table("book_catalog_pdfs_{$partCompanyId}_{$projectClassifyId}")->where('book_catalog_id', $val1->id)->update([
                                'book_title_id'   => $val2->book_title_id,
                                'book_catalog_id' => $val2->id,
                            ]); //下面 如果这条删除的数据，已经把原来的PDF转移到新卷内里面，就进行标记，之后忽略
                            DB::table("book_catalogs_{$partCompanyId}_{$projectClassifyId}")->where('id', $val1->id)->update(['creator_id' => 0]);
                        }
                    }
                }
            }
        }
    }

    /**
     * 赵晶指定格式导入卷内
     */
    public function importCatalog($request)
    {

    }

    /**
     * 更新数据
     */
    public function updateData()
    {
        $list = DB::table('a_catalog_counts')->get();
        foreach ($list as $val) {
            $archivesNo = $val->archives_no;
            switch ($archivesNo) {
                case str_contains($val->archives_no, '5HQ'):
                    $major     = '涵渠';
                    $majorCode = 'HQ';
                    break;
                case str_contains($val->archives_no, '5HB'):
                    $major     = '环保';
                    $majorCode = 'HB';
                    break;
                case str_contains($val->archives_no, '5QL'):
                    $major     = '梁场';
                    $majorCode = 'QL';
                    break;
                case str_contains($val->archives_no, '5LJ'):
                    $major     = '路基';
                    $majorCode = 'LJ';
                    break;
                case str_contains($val->archives_no, '5XL'):
                    $major     = '线路';
                    $majorCode = 'XL';
                    break;
                case str_contains($val->archives_no, '5ZH'):
                    $major     = '综合';
                    $majorCode = 'ZH';
                    break;
                case (str_contains($val->archives_no, '5Z') && !str_contains($val->archives_no, '5ZH')):
                    $major     = '站场';
                    $majorCode = 'Z';
                    break;
                case (str_contains($val->archives_no, '5Q') && !str_contains($val->archives_no, '5QL')) :
                    $major     = '桥梁';
                    $majorCode = 'Q';
                    break;
                default:
                    $major     = '未知';
                    $majorCode = '';
                    break;
            }
            DB::table('a_catalog_counts')->where('id', $val->id)->update([
                'major'      => $major,
                'major_code' => $majorCode
            ]);
        }
    }

    /**
     * 从操作记录里面提取用户地址和时间
     *
     * @author: lxk
     * @date: 2024/6/15 15:16
     */
    public function getLoginInfo()
    {
        $address = ActionLog::query()->distinct()->pluck('address')->toArray();
        $address = array_filter($address);
        $apiKey  = config('site')['map_key'];
        $arr     = [];
        foreach ($address as $val) {
            $url              = 'https://restapi.amap.com/v3/geocode/geo?key=' . $apiKey . '&address=' . urlencode($val);
            $response         = file_get_contents($url);
            $jsonResponse     = json_decode($response, true);
            $lngLat           = $jsonResponse['geocodes'][0]['location'];
            $lngLat           = explode(',', $lngLat);
            $lng              = $lngLat[0];
            $lat              = $lngLat[1];
            $arr[$val]['lng'] = $lng;
            $arr[$val]['lat'] = $lat;
        }
        $address = ActionLog::query()->distinct()->select('user_id', 'user_name', 'address')->orderBy('user_id')->get()->toArray();
        foreach ($address as $val) {
            $loginTime = ActionLog::query()->where('user_id', $val['user_id'])->where('address', $val['address'])->orderByDesc('id')->value('created_at');
            if ($val['address']) {
                UserLoginInfo::query()->insert([
                    'user_id'       => $val['user_id'],
                    'username'      => $val['user_name'],
                    'login_address' => $val['address'],
                    'login_lng'     => $arr[$val['address']]['lng'],
                    'login_lat'     => $arr[$val['address']]['lat'],
                    'login_time'    => $loginTime,
                ]);
            }
        }
    }


    /**
     * 把单位工程里面的major_code和档号匹配
     */
    public function updateMajorCode()
    {
        $list = ProjectClassify::query()->select('id', 'archives_no', 'stage_code', 'major_code')->get()->toArray();
        foreach ($list as $val) {
            $info = ProjectClassifyService::sliceArchivesNo($val['archives_no']);
            ProjectClassify::query()->where('id', $val['id'])->update([
                'stage_code' => $info['stage_code'],
                'major_code' => $info['major_code'],
            ]);
        }
    }

    public function projectSearch($request)
    {
        $tables    = [];
        $baseTable = $request->input('base_table');
        $baseTable = $baseTable . '_';
        $allTables = DB::connection()->getDoctrineSchemaManager()->listTableNames(); //获取全部数据表名
        foreach ($allTables as $table) {
            if (str_contains($table, $baseTable)) {
                $tmp['table'] = $table;
                $tmp['count'] = DB::table($table)->count('id');
                $tables[]     = $tmp;
            }
        }
        $countArr = array_column($tables, 'count');
    }


    public static function getPartAndProjectClassifyIds()
    {
        $partCompanyIds = PartCompanyClassify::query()
            ->distinct()
            //->where('part_company_id', '!=', 28) //part_company_id=28的数据表数据量大，单独处理
            ->orderBy('part_company_id')
            ->pluck('part_company_id')
            ->toArray();

        $projectClassifyIds = PartCompanyClassify::query()->distinct()
            ->orderBy('project_classify_id')
            ->pluck('project_classify_id')
            ->toArray();

        return [
            'part_company_ids'     => $partCompanyIds,
            'project_classify_ids' => $projectClassifyIds,
        ];
    }


    /**
     * @author: lxk
     * @date: 2024/1/4 11:21
     * 格式化项目分类里面的不规范的档案号
     */
    public function formatProjectClassifyArchivesNo()
    {
        $list = ProjectClassify::query()->select('id', 'archives_no')->get()->toArray();
        foreach ($list as $val) {
            ProjectClassify::query()->where('id', $val['id'])->update(['archives_no' => format_archives_no($val['archives_no'])]);
        }
    }

    public function clearPdf()
    {
        $idsInfo            = self::getPartAndProjectClassifyIds();
        $partCompanyIds     = $idsInfo['part_company_ids'];
        $projectClassifyIds = $idsInfo['project_classify_ids'];
        foreach ($partCompanyIds as $partCompanyId) {
            foreach ($projectClassifyIds as $projectClassifyId) {
                $table1 = "book_titles_{$partCompanyId}_{$projectClassifyId}";
                $table2 = "book_catalogs_{$partCompanyId}_{$projectClassifyId}";
                $table3 = "book_catalog_pdfs_{$partCompanyId}_{$projectClassifyId}";
                if (Schema::hasTable($table1)) {
                    if (Schema::hasTable($table2) && Schema::hasTable($table3)) {
                        if (DB::table($table2)->whereNotNull('deleted_at')->value('id')) { //如果有删除的数据
                            $delPdfs = DB::table($table2)
                                ->join($table3, "{$table2}.id", '=', "{$table3}.book_catalog_id")
                                ->whereNotNull('deleted_at')
                                ->select("{$table2}.id", "{$table3}.file_url", "{$table3}.origin_name")
                                ->get();

                            if ($delPdfs->isNotEmpty()) {
                                $batchArr = [];
                                foreach ($delPdfs as $key => $val) {
                                    $batchArr[$key]['table_name']  = $table2;
                                    $batchArr[$key]['catalog_id']  = $val->id;
                                    $batchArr[$key]['file_url']    = $val->file_url;
                                    $batchArr[$key]['origin_name'] = $val->origin_name;
                                }
                                info($table2 . '---' . count($delPdfs->toArray()));
                                DB::table('del_pdfs_copy')->insert($batchArr);
                            }
                        }
                    }
                }
            }
        }
    }

    /**
     * 批量修改档案序号后缀数字
     * 档案号后缀原范围 2830-3320
     * 后缀范围更改为
     */
    public function updateArchivesNoExt($request)
    {
        $partCompanyId     = $request->input('part_company_id');
        $projectClassifyId = $request->input('project_classify_id');
        $bookTitleInstance = $this->getBookTitleInstance($partCompanyId, $projectClassifyId);

        $list     = $bookTitleInstance->select('id', 'serial_no', 'archives_no')->get()->toArray();
        $serialNo = 2179;
        foreach ($list as $val) {
            $serialNo++;
            $mainArchivesNo = substr($val['archives_no'], 0, strrpos($val['archives_no'], '-')); //获取主档案号
            info($serialNo . '----------------------' . $mainArchivesNo . '-' . $serialNo);
            $bookTitleInstance->where('id', $val['id'])->update(['serial_no' => $serialNo, 'archives_no' => $mainArchivesNo . '-' . $serialNo]);
        }
    }

    /**
     * 获取数据表拷贝的前后数据对比
     */
    public function compareBeforeAndAfter()
    {
        $ext    = '117_549';
        $count0 = DB::table("book_catalogs_{$ext}")->count('id');
        $count1 = DB::table("book_catalogs_{$ext}")->whereNotNull('deleted_at')->count('id');
        $count2 = DB::table("book_catalogs_{$ext}")->whereNull('deleted_at')->count('id');
        $count3 = DB::table("book_catalogs_{$ext}_copy")->count('id');

        aa('总数:' . $count0 . '; 无效:' . $count1 . ';有效:' . $count2 . ';当前:' . $count3);
    }

    /**
     * 删除空数据表 （案卷和卷内分表）
     */
    public function delEmptyTable()
    {
        $idsInfo            = $this->getPartAndProjectClassifyIds();
        $partCompanyIds     = $idsInfo['part_company_ids'];
        $projectClassifyIds = $idsInfo['project_classify_ids'];

        $hadSoftDelProjectClassifyIds = ProjectClassify::onlyTrashed()->pluck('id')->toArray();

        foreach ($partCompanyIds as $partCompanyId) {
            foreach ($hadSoftDelProjectClassifyIds as $hadSoftDelProjectClassifyId) {
                $table1 = "book_titles_{$partCompanyId}_{$hadSoftDelProjectClassifyId}";
                $table2 = "book_catalogs_{$partCompanyId}_{$hadSoftDelProjectClassifyId}";
                $table3 = "book_catalog_pdfs_{$partCompanyId}_{$hadSoftDelProjectClassifyId}";
                if (Schema::hasTable($table1)) {
                    Schema::drop($table1);
                }
                if (Schema::hasTable($table2)) {
                    Schema::drop($table2);
                }
                if (Schema::hasTable($table3)) {
                    Schema::drop($table3);
                }
            }
            foreach ($projectClassifyIds as $projectClassifyId) {
                $table1 = "book_titles_{$partCompanyId}_{$projectClassifyId}";
                $table2 = "book_catalogs_{$partCompanyId}_{$projectClassifyId}";
                $table3 = "book_catalog_pdfs_{$partCompanyId}_{$projectClassifyId}";
                if (Schema::hasTable($table1)) {
                    $count = DB::table($table1)->count('id');
                    if (empty($count)) { //如果分表没数据，删除分表
                        Schema::drop($table1);
                    }
                }
                if (Schema::hasTable($table2)) {
                    $count = DB::table($table2)->count('id');
                    if (empty($count)) { //如果分表没数据，删除分表
                        Schema::drop($table2);
                    }
                }
                if (Schema::hasTable($table3)) {
                    $count = DB::table($table3)->count('id');
                    if (empty($count)) { //如果分表没数据，删除分表
                        Schema::drop($table3);
                    }
                }
            }
        }
    }

    /**
     * 清空每次 【检查案卷】后的问题标记和案卷起止日期  book_catalog_pdfs
     * part_company_id : 15  28  31  33  34
     */
    public function copyTable(Request $request)
    {
        $idsInfo            = $this->getPartAndProjectClassifyIds();
        $partCompanyIds     = $idsInfo['part_company_ids'];
        $projectClassifyIds = $idsInfo['project_classify_ids'];
        foreach ($partCompanyIds as $partCompanyId) {
            foreach ($projectClassifyIds as $projectClassifyId) {
                $table1 = "book_catalogs_{$partCompanyId}_{$projectClassifyId}";
                if (Schema::hasTable($table1)) {
                    if (DB::table($table1)->whereNotNull('deleted_at')->value('id')) { //如果有删除的数据
                        CopyTable::dispatch($partCompanyId, $projectClassifyId);
                    }
                }
            }
        }

        return $this->success('', '操作成功！');
    }

    /**
     * 创建用户和公司关系
     */
    public function linkCompanyUser()
    {
        $userIds = User::query()->pluck('id')->toArray();
        foreach ($userIds as $userId) {
            $list = PartCompanyUser::query()->where('user_id', $userId)->get()->toArray();
            foreach ($list as $val) {
                $companyId = PartCompany::query()->where('id', $val['part_company_id'])->value('company_id');
                $info      = CompanyUser::query()->where([
                    'user_id'    => $userId,
                    'company_id' => $companyId
                ])->first();
                if (!$info) {
                    info($userId . '------' . $companyId);
                    $companyUser             = new CompanyUser();
                    $companyUser->company_id = $companyId ?: 0;
                    $companyUser->user_id    = $userId;
                    $companyUser->is_admin   = 0;
                    $companyUser->creator_id = user_id();
                    $companyUser->save();
                }
            }
        }
    }

    /**
     * 导出卷内目录
     * @author: lxk
     * @date: 2023/5/27 16:31
     */
    public function export(Request $request)
    {
        $partCompanyId     = $request->input('part_company_id');
        $projectClassifyId = $request->input('project_classify_id');

        $partCompany = PartCompany::query()->where('id', $partCompanyId)->first();
        $project     = Project::query()->find($partCompany->project_id);

        $bookTitleInstance = $this->getBookTitleInstance($partCompanyId, $projectClassifyId);
        $bookTitleIds      = $bookTitleInstance
            ->distinct()
            ->orderBy('serial_no')
            ->pluck('id')
            ->toArray();
        if (!empty($bookTitleIds)) {
            $eachChunk   = 20;
            $arrChunks   = array_chunk($bookTitleIds, $eachChunk);  //选中的book_title_ids分割，每部分几个元素
            $fileCount   = ceil(count($bookTitleIds) / $eachChunk); //会拆分成几个excel
            $baseDirName = DownloadJobService::getDirName($bookTitleInstance, $bookTitleIds);
            $dirName     = 'excels/' . $partCompany->title . '_' . $baseDirName . date('His');
            CommonService::createDir($dirName);
            $downloadJobId = (new DownloadJobService())->addNewData($partCompanyId, $projectClassifyId, $bookTitleIds, $dirName, $fileCount); //添加到任务表
            CommonService::addProjectActionLog("导出卷内目录,excel文件数量{$fileCount}", 0, $partCompanyId, $projectClassifyId);
            foreach ($arrChunks as $arr) { //按照逻辑，必须先把数据存到redis再执行到处任务
                $childScope = DownloadJobService::getChildSerialNos($bookTitleInstance, $arr);
                $data       = [
                    'user_id'             => user_id(),
                    'download_job_id'     => $downloadJobId,
                    'part_company_id'     => $partCompanyId,
                    'project_classify_id' => $projectClassifyId,
                    'book_title_ids'      => implode(',', $arr),
                    'file_name'           => $dirName . '/' . $childScope . '.xlsx',
                    'created_at'          => date('Y-m-d H:i:s'),
                    'status'              => 0,
                ];
                DownloadJobQueue::query()->insert($data);
            }
            $firstIds        = $arrChunks[0]; //把第一个进行导出
            $firstChildScope = DownloadJobService::getChildSerialNos($bookTitleInstance, $firstIds);
            $firstFileName   = $dirName . '/' . $firstChildScope . '.xlsx';
            unset($arrChunks[0]); //去掉第一个
            switch ($project->combination_id) { //用的哪种卷内格式
                case 2: //普通8号文格式
                    (new BookCatalog8($partCompanyId, $projectClassifyId, $firstIds))->store('public/' . $firstFileName)->chain([
                        new SlowExeCatalog8($downloadJobId)
                    ]);
                    break;
                default: //经典29号文
                    (new BookCatalogExport29($partCompanyId, $projectClassifyId, $firstIds))->store('public/' . $firstFileName)->chain([
                        new SlowExeExportA($downloadJobId)
                    ]);
                    break;
            }
        }

        return $this->success('', '已加入下载任务！');
    }

    /**
     * 导出一个参建单位的卷内数据
     */
    public function export2(Request $request)
    {
        $partCompanyId = $request->input('part_company_id');
        $partCompany   = PartCompany::query()->where('id', $partCompanyId)->first();

        $project = Project::query()->find($partCompany->project_id);

        $projectClassifyIds = PartCompanyClassify::query()
            ->distinct()
            ->where('part_company_id', $partCompanyId)
            ->orderBy('project_classify_id')
            ->pluck('project_classify_id')
            ->toArray();

        foreach ($projectClassifyIds as $projectClassifyId) {
            $table1 = "book_titles_{$partCompanyId}_{$projectClassifyId}";
            if (Schema::hasTable($table1)) {
                $btCount1 = DB::table($table1)->whereNull('deleted_at')->count('id'); //有效数据
                $btCount2 = DB::table($table1)->count('id'); //全部数据
                if (empty($btCount1) || empty($btCount2)) {
                    Schema::drop($table1);
                } else {
                    $bookTitleInstance = $this->getBookTitleInstance($partCompanyId, $projectClassifyId);
                    $bookTitleIds      = $bookTitleInstance
                        ->distinct()
                        ->orderBy('serial_no')
                        ->pluck('id')
                        ->toArray();
                    if (!empty($bookTitleIds)) {
                        $eachChunk   = 20;
                        $arrChunks   = array_chunk($bookTitleIds, $eachChunk);  //选中的book_title_ids分割，每部分几个元素
                        $fileCount   = ceil(count($bookTitleIds) / $eachChunk); //会拆分成几个excel
                        $baseDirName = DownloadJobService::getDirName($bookTitleInstance, $bookTitleIds);
                        $dirName     = 'excels/' . $partCompany->title . '_' . $baseDirName . date('His');
                        CommonService::createDir($dirName);
                        $downloadJobId = (new DownloadJobService())->addNewData($partCompanyId, $projectClassifyId, $bookTitleIds, $dirName, $fileCount); //添加到任务表
                        CommonService::addProjectActionLog("导出卷内目录,excel文件数量{$fileCount}", 0, $partCompanyId, $projectClassifyId);
                        foreach ($arrChunks as $arr) { //按照逻辑，必须先把数据存到redis再执行到处任务
                            $childScope = DownloadJobService::getChildSerialNos($bookTitleInstance, $arr);
                            $data       = [
                                'user_id'             => user_id(),
                                'download_job_id'     => $downloadJobId,
                                'part_company_id'     => $partCompanyId,
                                'project_classify_id' => $projectClassifyId,
                                'book_title_ids'      => implode(',', $arr),
                                'file_name'           => $dirName . '/' . $childScope . '.xlsx',
                                'created_at'          => date('Y-m-d H:i:s'),
                                'status'              => 0,
                            ];
                            DownloadJobQueue::query()->insert($data);
                        }
                        $firstIds        = $arrChunks[0]; //把第一个进行导出
                        $firstChildScope = DownloadJobService::getChildSerialNos($bookTitleInstance, $firstIds);
                        $firstFileName   = $dirName . '/' . $firstChildScope . '.xlsx';
                        unset($arrChunks[0]); //去掉第一个
                        switch ($project->combination_id) { //用的哪种卷内格式
                            case 2: //普通8号文格式
                                (new BookCatalog8($partCompanyId, $projectClassifyId, $firstIds))->store('public/' . $firstFileName)->chain([
                                    new SlowExeCatalog8($downloadJobId)
                                ]);
                                break;
                            default: //经典29号文
                                (new BookCatalogExport29($partCompanyId, $projectClassifyId, $firstIds))->store('public/' . $firstFileName)->chain([
                                    new SlowExeExportA($downloadJobId)
                                ]);
                                break;
                        }
                    }
                }
            }
        }
        return $this->success('', '已加入下载任务！');
    }

    /**
     * 给检索阶段造一批已审批的数据
     */
    public function createSomeSearchData()
    {
        $partCompanyIds     = PartCompany::query()->pluck('id')->toArray();
        $projectClassifyIds = ProjectClassify::query()->pluck('id')->toArray();

        foreach ($partCompanyIds as $partCompanyId) {
            foreach ($projectClassifyIds as $projectClassifyId) {
                $table = "book_titles_{$partCompanyId}_{$projectClassifyId}";
                if (Schema::hasTable($table)) {
                    DB::table($table)
                        ->whereNull('deleted_at')
                        ->where('status', 0)
                        ->orderBy('id')
                        ->limit(35)
                        ->update(['status' => 2]);
                }
            }
        }
    }

    /**
     * 脚本
     */
    public function exeScript(Request $request)
    {
        $partCompanyId      = $request->input('part_company_id');
        $projectClassifyIds = $this->getProjectClassidyIds($partCompanyId);
        foreach ($projectClassifyIds as $projectClassifyId) {
            $table = "book_titles_{$partCompanyId}_{$projectClassifyId}";
            $this->serialNoSameExt($table);
        }
    }

    /**
     * 获取参见单位下全部项目分类id
     */
    public function getProjectClassidyIds($partCompanyId)
    {
        $partCompany     = PartCompany::query()->find($partCompanyId);
        $templateId      = Project::query()->where('id', $partCompany->project_id)->value('template_id');
        $partCompanyType = $partCompany->type; //参见单位的类型

        $ids = ProjectClassify::query()
            ->where('template_id', $templateId)
            ->where('part_company_type', $partCompanyType) //基础项目分类
            ->orWhere('part_company_id', $partCompanyId) //参见单位后期自己加的
            ->pluck('id')
            ->toArray();
        /**
         * 下面只要有案卷的项目分类，避免过多无效任务队列
         */
        return array_unique($ids);
    }

    /**
     * 获取档案号后缀，补齐0，例如 2，返回 0002
     */
    public function ext()
    {
        $archivesNo = 'J•XJKZ•5S270-6';
        $archivesNo = format_archives_no($archivesNo);
        $endPos     = strrpos($archivesNo, '-');
        $extNum     = substr($archivesNo, $endPos + 1);
        $extLen     = strlen($extNum);
        if ($extLen == 1) {
            $val = '000' . $extNum;
        } elseif ($extLen == 2) {
            $val = '00' . $extNum;
        } elseif ($extLen == 3) {
            $val = '0' . $extNum;
        } else {
            $val = $extNum;
        }
        return (string)$val;
    }

    /**
     * 把案卷数据表里面的序号值，和档案号后缀保持一致
     */
    public function serialNoSameExt($table)
    {
        if (Schema::hasTable($table)) {
            DB::table($table)->chunkById(100, function ($list) use ($table) {
                foreach ($list as $val) {
                    $archivesNo = $val->archives_no;
                    $endPos     = strrpos($archivesNo, '-');
                    $extNum     = substr($archivesNo, $endPos + 1);
                    DB::table($table)->where('id', $val->id)->update(['serial_no' => $extNum]);
                }
            });
        }
    }

    /**
     * 科技档案
     * 获取【案卷】表实例
     */
    public function getBookTitleInstance($partCompanyId, $projectClassifyId)
    {
        if (empty($partCompanyId) || empty($projectClassifyId)) {
            throw new ApiException('缺少必要参数！');
        }
        $table = "book_titles_{$partCompanyId}_{$projectClassifyId}";
        if (!Schema::hasTable($table)) {
            DB::update("create table if not exists {$table}   like book_titles");
        }
        $bookCatalog = new BookCatalog();

        return $bookCatalog->setTable($table);
    }

    /**
     * 科技档案
     * 获取【卷内】表实例
     */
    public function getBookCatalogInstance($partCompanyId, $projectClassifyId)
    {
        if (empty($partCompanyId) || empty($projectClassifyId)) {
            throw new ApiException('缺少必要参数！');
        }
        $table = "book_catalogs_{$partCompanyId}_{$projectClassifyId}";
        if (!Schema::hasTable($table)) {
            DB::update("create table if not exists {$table} like book_catalogs");
        }
        $bookCatalog = new BookCatalog();

        return $bookCatalog->setTable($table);
    }

    /**
     * 科技档案
     * 获取【卷内 pdf】表实例
     */
    public function getBookCatalogPdfInstance($partCompanyId, $projectClassifyId)
    {
        if (empty($partCompanyId) || empty($projectClassifyId)) {
            throw new ApiException('缺少必要参数！');
        }
        $table = "book_catalog_pdfs_{$partCompanyId}_{$projectClassifyId}";
        if (!Schema::hasTable($table)) {
            DB::update("create table if not exists {$table}   like book_catalog_pdfs");
        }
        $catalogPdf = new BookCatalogPdf();

        return $catalogPdf->setTable($table);
    }

    /**
     * 照片档案
     * 获取【案卷】表实例
     */
    public function getPhotoArchiveInstance($partCompanyId, $photoClassifyId)
    {
        if (empty($partCompanyId) || empty($photoClassifyId)) {
            throw new ApiException('缺少必要参数！');
        }
        $table = "photo_archives_{$partCompanyId}_{$photoClassifyId}";
        if (!Schema::hasTable($table)) {
            DB::update("create table if not exists {$table}  like photo_archives");
        }
        $bookCatalog = new BookCatalog();

        return $bookCatalog->setTable($table);
    }

    /**
     * 照片档案
     * 获取【卷内】表实例
     */
    public function getPhotoCatalogInstance($partCompanyId, $photoClassifyId)
    {
        if (empty($partCompanyId) || empty($photoClassifyId)) {
            throw new ApiException('缺少必要参数！');
        }
        $table = "photo_catalogs_{$partCompanyId}_{$photoClassifyId}";
        if (!Schema::hasTable($table)) {
            DB::update("create table if not exists {$table} like photo_catalogs");
        }
        $bookCatalog = new BookCatalog();

        return $bookCatalog->setTable($table);
    }

    /**
     * 照片档案
     * 获取【卷内照片】表实例
     */
    public function getPhotoImgInstance($partCompanyId, $photoClassifyId)
    {
        if (empty($partCompanyId) || empty($photoClassifyId)) {
            throw new ApiException('缺少必要参数！');
        }
        $table = "photo_imgs_{$partCompanyId}_{$photoClassifyId}";
        if (!Schema::hasTable($table)) {
            DB::update("create table if not exists {$table} like photo_imgs");
        }
        $bookCatalog = new BookCatalog();

        return $bookCatalog->setTable($table);
    }

    /**
     * 把不规范的档案号，经过清洗，变为规范的档案号
     */
    public function formatArchivesNo(Request $request)
    {
        $partCompanyId = $request->input('part_company_id');
        $tableName     = "book_titles_{$partCompanyId}";

        $list = DB::table($tableName)->get()->toArray();
        foreach ($list as $val) {
            $pos        = strpos($val->archives_no, '-');
            $base       = substr($val->archives_no, 0, $pos + 1); //档案号前面
            $num        = substr($val->archives_no, $pos + 1); //档案号后缀
            $archivesNo = "$base{$num}";
            $archivesNo = format_archives_no($archivesNo);
            DB::table($tableName)->where('id', $val->id)->update(['archives_no' => $archivesNo]);
        }
    }

    /**
     * 把不规范的页号变为规范的
     */
    public function formatPageNum()
    {
        DB::table('book_catalogs_17')->chunkById(100, function ($catalogs) {
            foreach ($catalogs as $catalog) {
                $pageNum = format_page_num($catalog->page_num);
                DB::table('book_catalogs_17')->where('id', $catalog->id)->update(['page_num' => $pageNum]);
            }
        });
    }

    /**
     * 测试 获取上传的blob  form-data,返回文件地址
     * 'origin_name' => 'Sheet1',
     * 'file' => 'files/20230328/708ddc16e6222192c99ca9788d28754f.xlsx',
     * 'base_url' => 'http://www.newrailway.com/storage/'
     */
    public function excelToPdf(Request $request)
    {
        $file        = $request->file('file');
        $result      = (new AttachmentService())->upload($file, 'local');
        $spreadsheet = IOFactory::load("storage/{$result['file']}");
        $writer      = new Mpdf($spreadsheet);

        $writer->save("storage/excelToPdf/{$result['origin_name']}.pdf");

        return Storage::disk('public')->download("excelToPdf/{$result['origin_name']}.pdf");
    }


}
